package com.bit.cfdi.web.domain;

import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.Mapping;
import org.hibernate.mapping.AbstractAuxiliaryDatabaseObject;

/**@author cpalalia
 * Objetos Auxiliares de la Base de Datos
 * Trigger que actualiza el historial de las claves del usuario*/
public class DBUtilities extends AbstractAuxiliaryDatabaseObject  {

	private static final long serialVersionUID = 6267250869895192442L;

	
	public String sqlCreateString(Dialect dialect, Mapping mapping, String defaultCatalog,
			String defaultSchema) throws HibernateException {
		String statement = "	CREATE TRIGGER TR_PASS_UPDATER AFTER INSERT ON USUARIO " +
										"FOR EACH ROW " +
										"			BEGIN " +
										"				DECLARE REGISTROS INT; " +
										"				DECLARE REG_MIN INT; " +
										"				DECLARE REG_MAX INT; " +
										"				DECLARE HOY DATETIME; " +
										"				SELECT COUNT(*) INTO REGISTROS FROM HISTORIAL_PASSWORD; " +
										"				SELECT NOW() INTO HOY; " +
										"				if REGISTROS is not null and REGISTROS > 2 then " +
										"				 	SELECT MIN(id_historial_password) INTO REG_MIN FROM HISTORIAL_PASSWORD; " +
										" 					 	DELETE FROM HISTORIAL_PASSWORD WHERE id_historial_password = REG_MIN; " +
										"				 	INSERT INTO HISTORIAL_PASSWORD (fecha, fecha_modificacion, password, id_usuario) " +
										"				       VALUES (HOY, HOY, NEW.password, NEW.id_usuario); " +
										"				else " +
										"					INSERT INTO HISTORIAL_PASSWORD (fecha, fecha_modificacion, password, id_usuario) " +
										"				       	VALUES (HOY, HOY, NEW.password, NEW.id_usuario); " +
										"				end if; " +
										"			END";
		return statement;
	}

	public String sqlDropString(Dialect arg0, String defaultCatalog, String defaultSchema) {
		return "DROP TRIGGER IF EXISTS TR_PASS_UPDATER";
	}

}
